Amazon Redshift: UDF(User-Defined Functions:ユーザー独自の定義関数)の作成方法

しんや

2015.09.14

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

先日、Amazon Redshiftに待望のUDF(User-Defined Functions:ユーザー独自の定義関数)に関する機能がリリースされた！という内容のエントリを投稿しました。

Amazon Redshift: UDF(User Defined Functions: ユーザー定義の独自関数)が遂にサポートされました！｜ Developers.IO

この発表に併せて、UDFの作成方法もドキュメントに展開されていましたので当エントリではその内容について読み解いて行きたいと思います。

Creating User-Defined Functions - Amazon Redshift

はじめに
UDFに関する制約
UDFのセキュリティと権限
UDF 対応データタイプ
- ANYELEMENTデータ型
UDFの命名について
- 関数名のオーバーロード
- UDF 名前衝突の回避
スカラーUDFの作成
- スカラ関数のサンプル
UDFのPython言語対応
- カスタムPythonライブラリモジュールをインポートする
- RedshiftクラスタへのカスタムPythonモジュールのインポート手順
まとめ

はじめに

この度、Amazon Redshiftに於いて、Pythonプログラムに基づいた非SQL処理を実装するための独自の『ユーザー定義スカラ関数(UDF: user-defined scalar function)』を作成する事が出来るようになりました。

新しい関数はデータベースに格納され、既存のAmazon Redshiftに於ける関数を実行するのと同じような使い方で、実行権限を持つユーザーであれば誰でも使えるようになります。

Pythonの標準機能を使う事が出来るのに加えて、独自に作成したカスタムPythonモジュールをインポートする事も出来ます。この辺りの詳細については『UDFのPython言語対応』の項をご参照ください。

UDFを作成するためには、Python言語のUSAGE権限が必要となります。UDFの所有者又はスーパーユーザーがUDFを実行する事が出来ます。その他のユーザーが使用する場合は各関数に対して権限が付与されている必要があります。権限の詳細については『UDFのセキュリティと権限』の項をご参照ください。

UDFに関する制約

Amazon RedshiftでUDFを扱う際の制約には以下のようなものがあります。

ネットワークへのアクセス、またはファイルシステムへの書き込みは出来ません。
ユーザーがインストールしたライブラリの合計サイズは100MBまでです。
クラスタ毎に同時実行出来るUDFの数は、クラスタの同時実行可能数の合計の4分の1までです。例えばクラスタ側で同実行可能数が15に設定されていた場合、UDFの同時実行可能数は3となります。この制限数に達すると、内部的にはUDFはWLM管理キュー内に入り、実行待ちの状態となります。この辺りの詳細についてはImplementing Workload Managementをご参照ください。

UDFのセキュリティと権限

スーパーユーザーはデフォルトで全ての権限を有しています。その他のユーザーがUDFを実行するためには以下の権限が必要となります。

UDF又はUDFライブラリを作成するために、USAGE ON LANGUAGE plpythonuを記載する必要があります。
UDFまたはライブラリを置き換えたり削除したりするために、所有者またはスーパーユーザーである必要があります。
UDFを実行するためには、各関数毎にEXECUTE ON FUNCTIONを記載する必要があります。デフォルトでは、PUBLICユーザーグループは新しいUDFに対する実行権限を持っています。使用を制限するためには、一旦PUBLICユーザーグループからEXECUTEを剥奪(revoke)し、任意の個人またはグループのみに権限を付与するようにします。この辺りの詳細についてはGRANT, REVOKEをご参照ください。

UDF 対応データタイプ

UDFでは、入力値及び関数の返り値に、標準的なAmazon Redshiftのデータ型をひと通り扱う事が出来ます。標準的なデータ型に加え、UDFはデータ型:ANYELEMENTをサポートします。これはAmazon Redshiftが実行時に指定された引数に基いて、標準的なデータ型への変換を自動的に行うものです。スカラUDFはANYELEMENTのデータ型を返す事が出来ます。詳細は『ANYELEMENTデータ型』をご参照ください。

実行中に、Amazon RedshiftはAmazon Redshift側で受け取ったデータ型の引数からPythonでの処理を行うためのデータ型に変換を行います。そして(Pythonで)処理を行い、Pythonのデータ型から対応するAmazon Redshiftのデータ型への変換を行い、返り値として返します。

以下表はAmazon RedshiftとPythonのデータ型対応表となります。

Amazon Redshiftデータ型	Pythonデータ型
smallint integer bigint short long	int
decimal or numeric	decimal
double real	float
boolean	bool
char varchar	string
timestamp	datetime

ANYELEMENTデータ型

ANYELEMENTはpolymorphic(多様な形を持つ事が出来る)なデータ型です。これは意味します。もし関数の中で引数のデータ型に対しANYELEMENTが宣言されていた場合、関数は実行時に引数としてどの(標準的な)Amazon Redshiftデータ型でも受け入れる事が出来ます。ANYELEMENT引数は関数が呼び出された際に、実際に渡されたデータ型に設定がなされます。

もし関数が複数のANYELEMENT型を使っていた場合、関数呼び出し時に同じ実際のデータ型に対して全て解決する必要があります。全てのANYELEMENT引数データ型は最初の引数のANYELEMENTに渡される実際のデータ型の種類に対して設定されます。

例えば、f_equal(anyelement, anyelement)と宣言された関数は、任意の2つの入力値を取りますが、それらは同じデータ型となります。また、関数の戻り値にANYELEMENTが宣言されていた場合、少なくとも引数の入力値のうち1つはANYELEMENTである必要があります。実際の戻り値のデータ型は入力引数の実際のデータ型と同じになります。

UDFの命名について

実装前にUDFの命名規則を考慮しておく事で、潜在的な競合と予期しない結果を回避する事が出来ます。関数名はオーバーロードする事が出来るので、Amazon Redshiftの既存又は今後提供される関数名と名前が衝突する可能性があります。この項では、オーバーロードの説明及び衝突回避のための戦略について説明します。

関数名のオーバーロード

関数はその関数名とシグネチャ(入力引数と引数のデータ型)によって識別されます。異なるシグネチャであれば、同じスキーマ内であっても同じ名前を持つことが可能です。言い換えれば、関数名はオーバーロード出来るのです。

クエリを実行すると、クエリエンジンは引数の数、引数のデータ型に基づいて呼び出す関数を決定します。CREATE FUNCTIONコマンドで許可されている最大数の可変個の引数を持つ関数をシミュレートするためにオーバーロードを使用する事が出来ます。

UDF 名前衝突の回避

プレフィックス: f_を使ってUDFの命名を行う事をお勧めします。自作の関数にf_が付いた名前を付ける事によってAmazon RedshiftはUDFのf_プレフィックスを予約し、UDFの名前が既存または将来的にAmazon Redshiftが提供する組込みのSQL関数名と重複せずに済みます。例えば、新しいUDF関数名にf_sumを命名する事で、Amazon RedshiftのSUM関数との名前衝突を回避する事が出来ます。同様に、新しい関数名にf_fibonacciと命名しておく事で、Amazon Redshiftが将来のリリースでFIBONACCIという関数を追加したとしても名前衝突せずに済みます。

UDFと組み込み関数が異なるスキーマに存在する場合、関数名がオーバーロードされない既存のAmazon Redshift組込み関数として同じ名前とシグネチャを持つUDFを作成する事が出来ます。組込み関数がシステムカタログスキーマであるpg_catalogに存在しているため、public、またはユーザー定義のスキーマのような同じ名前で異なるスキーマにUDFを作成する事が出来ます。明示的にスキーマ名指定されていない関数を呼び出す時、Amazon Redshiftはデフォルトでpg_catalogスキーマを検索し、同名のUDFをよりも前に組み込み関数を実行します。

search_pathの設定を、pg_catalogスキーマが参照順序の最後となるように変更する事でUDFの実行順序を優先させる事も出来ますが、予期しない結果を引き起こす可能性があります。なので、f_をプレフィックスで関数名に付ける等の戦略を採るほうがより確実でしょう。この辺りの詳細についてはSET, search_pathをご参照ください。

スカラーUDFの作成

スカラUDFにはPythonプログラムが組み込まれており、関数が呼ばれ、単一の値を返す際に実行を行います。 CREATE FUNCTIONコマンドでは、次のパラメータを定義します。

(オプション)入力引数。各引数は名前とデータ型を持ちます。
戻り値データ型x1
実行可能なPythonプログラムx1

CREATE FUNCTIONの構文は以下の通りです。

CREATE [ OR REPLACE ] FUNCTION f_function_name 
( [ argument_name arg_type [ , ... ] ] )
RETURNS data_type
{ VOLATILE | STABLE | IMMUTABLE }  
AS $$
  python_program
$$ LANGUAGE plpythonu;

Amazon RedshiftのクエリがスカラUDFを呼び出す際、実行時に以下の手順で処理が発生します。

1.関数は入力引数をPythonデータ型に変換。Amazon Redshiftのデータ型とPythonのデータ型のマッピング詳細については『UDF 対応データタイプ』をご参照ください。
2.関数は変換された入力引数を渡してPythonプログラムを実行。
3.Pythonコードは単一の値を返す。戻り値のデータ型は関数定義で指定されたデータ型を返す必要があります。
4.関数はPythonの戻り値を、指定したAmazon Redshiftのデータ型に変換し、クエリにその値を返す。

スカラ関数のサンプル

以下の例は、2つの数値を比較し、大きい方の値を返す関数です。2つのドル記号($$)の間のコードインデントが、Pythonの要件部分となる事に注意してください。詳細についてはCREATE FUNCTIONをご参照ください。

create function f_greater (a float, b float)
  returns float
stable
as $$
  if a > b:
    return a
  return b
$$ language plpythonu;

以下のクエリでは、SALESテーブルに問い合わせを行い、『COMMISSION』の値と『PRICEPAIDの20％』の値のうち、どちらか大きい方の値を返すf_greater関数を呼び出しています。

select f_greater (commission, pricepaid*0.20) from sales;

UDFのPython言語対応

Pythonプログラミング言語に基づく形でカスタムUDFを作成する事が出来ます。UDFの中では、以下のモジュールを除き、Python 2.7標準ライブラリを利用する事が出来ます。

ScrolledText
Tix
Tkinter
tk
turtle
smtpd

Pythonの標準ライブラリに加え、以下のモジュールはAmazon Redshiftの実装で利用されています。

また、独自のPythonモジュールをインポートする事が出来、 CREATE LIBRARYコマンドを実行する事でそれらをUDFの中で利用可能に出来るようになります。詳細についてはカスタムPythonライブラリモジュールをインポートするをご参照ください。 (重要：Amazon RedshifではUDFを介した全てのネットワークアクセス、ファイル書き込みが禁止されています。)

カスタムPythonライブラリモジュールをインポートする

Python言語シンタックスを用いてスカラ関数を定義します。ネイティブ標準ライブラリモジュールとAmazon Redshiftのプリインストールモジュールに加えて、独自のカスタムPythonライブラリモジュールを作成し、Redshiftクラスタにライブラリをインポートしたり、Pythonやサードパティから提供されている既存のライブラリを活用する事が出来ます。

Pythonの標準ライブラリモジュールやAmazon RedshiftにプリインストールされているPythonモジュールを含むライブラリを作成する事は出来ません。作成の際に、既存インストール済のライブラリ(ユーザーが作成したもの)が、同じPythonパッケージを使っていた場合、新しいライブラリをインストールする前に既存のライブラリをDROPしなければなりません。

カスタムライブラリをインストールするためには、スーパーユーザーであるか、またはUSAGE ON LANGUAGE plpythonu権限を持っている必要がありますが、関数を作成するための十分な権限を持っているユーザーであれば、ライブラリをインストールする事が出来ます。Redshiftクラスタに導入されているライブラリに関する情報を参照するためにPG_LIBRARYシステムカタログを照会する事が出来ます。(注：Amazon RedshiftではMicrosoft WindowsからのPythonモジュールのインポートはサポートしていません)

RedshiftクラスタへのカスタムPythonモジュールのインポート手順

このセクションではクラスタにカスタムPythonモジュールをインポートする例について見ていきます。このセクションで手順を実行するためには、ライブラリパッケージをアップロードするAmazon S3バケットを用意しておく必要があります。その後、クラスタ内部でパッケージのインストールを行います。以下例ではデータから距離を求めるUDFを作成しています。SQLクライアントツールからAmazon Redshiftクラスタに接続し、関数を作成するための以下のコマンドを実行します。

CREATE FUNCTION f_distance (x1 float, y1 float, x2 float, y2 float) RETURNS float IMMUTABLE as $$
    def distance(x1, y1, x2, y2):
        import math
        return math.sqrt((y2 - y1) ** 2 + (x2 - x1) ** 2)
 
    return distance(x1, y1, x2, y2)
$$ LANGUAGE plpythonu;
 
CREATE FUNCTION f_within_range (x1 float, y1 float, x2 float, y2 float) RETURNS bool IMMUTABLE as $$ 
    def distance(x1, y1, x2, y2):
        import math
        return math.sqrt((y2 - y1) ** 2 + (x2 - x1) ** 2)
 
    return distance(x1, y1, x2, y2) < 20
$$ LANGUAGE plpythonu;

2つめの宣言では、コードのうち数行が1つめの宣言と重複している事に注目。UDFは他のUDFの内容を参照出来ず、また双方の関数は同じ機能を必要とするので、この重複は必要(というか、止むを得ない対応)となります。しかし、複数の関数内でコードを重複させる代わりに、カスタムライブラリを作成し、そのカスタムライブラリを使うために機能を構成する事が出来ます。

この設定を行うには、まずはじめに以下の手順でライブラリのパッケージを作成します。

1.geometryという名前のフォルダを作成。このフォルダは、ライブラリの最上位パッケージです。
2.geometryフォルダ内に、__init__.pyという名前のファイルを作成。(注：このファイル名はアンダースコア2文字の連結部分を含みます。このファイルはpythonパッケージを初期化する事が出来る事を示します。)
3.geometryフォルダ内にtrigフォルダを作成。このフォルダはライブラリのサブパッケージです。
4.trigフォルダ内に別の__init__.pyとline.pyを作成します。このフォルダ内の__init__.pyはPythonに対して下位パッケージを初期化し、line.pyはライブラリコードが含まれるファイルとなります。フォルダ及びファイルの構造は、以下と同じにする必要があります。パッケージ構造に関する詳細な情報は、Python公式ドキュメントのModulesをご参照ください。

geometry/
   __init__.py
   trig/
      __init__.py
      line.py

5.以下のコードにはライブラリのクラスとメンバー関数が含まれます。コピーしてline.pyに貼り付けます。

class LineSegment:
  def __init__(self, x1, y1, x2, y2):
    self.x1 = x1
    self.y1 = y1
    self.x2 = x2
    self.y2 = y2
  def angle(self):
    import math
    return math.atan2(self.y2 - self.y1, self.x2 - self.x1)
  def distance(self):
    import math
    return math.sqrt((self.y2 - self.y1) ** 2 + (self.x2 - self.x1) ** 2)

パッケージ作成後、パッケージを準備してAmazon S3にアップロードするために以下の手順を行います。

1.geometryフォルダ配下のコンテンツを圧縮してgeometry.zipを作成します。geometryフォルダ自身は含まないようにしてください。以下に示すように、フォルダ配下のコンテンツのみを含むようにします。

geometry.zip
   __init__.py
   trig/
      __init__.py
      line.py

2.geometry.zipをAmazon S3バケットにアップロード。
3.SQLクライアントツールから、ライブラリをインストールするために以下のコマンドを実行。bucket_name,access key id,secret keyの内容はそれぞれ実行時の内容で置き換えてください。

CREATE LIBRARY geometry LANGUAGE plpythonu
FROM 's3://<bucket_name>/geometry.zip'
CREDENTIALS 'aws_access_key_id=<access key id>;aws_secret_access_key=<secret key>';

クラスタへライブラリをインストールした後、ライブラリを使うために関数の設定を行う必要があります。以下のコマンドを実行します。

CREATE OR REPLACE FUNCTION f_distance (x1 float, y1 float, x2 float, y2 float) RETURNS float IMMUTABLE as $$ 
    from trig.line import LineSegment
 
    return LineSegment(x1, y1, x2, y2).distance()
$$ LANGUAGE plpythonu;
 
CREATE OR REPLACE FUNCTION f_within_range (x1 float, y1 float, x2 float, y2 float) RETURNS bool IMMUTABLE as $$ 
    from trig.line import LineSegment
 
    return LineSegment(x1, y1, x2, y2).distance() < 20
$$ LANGUAGE plpythonu;

上記のコマンドでは、import trig/lineは、このセクションの元の関数からコードの重複を排除します。これで、複数のUDFでこのライブラリによって提供される関数を再利用する事が出来ます。(注：モジュールをインポートするために、サブパッケージとモジュール名をパスに指定する必要があります。(例：trig/line))

まとめ

以上、Amazon RedshiftのUDF(User-Defined Functions:ユーザー独自の定義関数)の作成方法に関するご紹介でした。現時点でも、リージョンによっては新規作成する事で既にこの機能を使う事も出来るようです。タイミングを見計らって、実際にUDFを作成・活用する部分についても実践・ご紹介してみたいと思います。こちらからは以上です。